﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class BookingDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Get all bookings by booking status
        /// </summary>
        /// <param name="status">Represent's the booking status (pending / cancelled / rejected / approved / settled)</param>
        /// <returns>Bookings</returns>
        public static List<Booking> GetAllBookings(string status)
        {
            List<Booking> bookings = new List<Booking>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllBookingsByStatus", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@Status", status);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Booking booking = new Booking
                        {
                            BookingId = Convert.ToInt32(reader["BookingId"]),
                            CustomerId = Convert.ToInt32(reader["CustomerId"]),
                            Type = reader["Type"].ToString(),
                            BookedDateTime = Convert.ToDateTime(reader["BookedDateTime"]),
                            OriginDateTime = Convert.ToDateTime(reader["OriginDateTime"]),
                            Status = status
                        };

                        //Following are possible nullable columns

                        if (reader["SupervisorId"] != DBNull.Value)
                        {
                            booking.SupervisorId = Convert.ToInt32(reader["SupervisorId"]);
                        }

                        if (reader["TravelledDistance"] != DBNull.Value)
                        {
                            booking.TravelledDistance = Convert.ToInt32(reader["TravelledDistance"]);
                        }

                        if (reader["DeliveredDateTime"] != DBNull.Value)
                        {
                            booking.DeliveredDateTime = Convert.ToDateTime(reader["DeliveredDateTime"]);
                        }

                        bookings.Add(booking);
                    }
                }
            }
            return bookings;
        }

        /// <summary>
        ///     Get all bookings
        /// </summary>
        /// <returns>Bookings</returns>
        public static List<Booking> GetAllBookings()
        {
            List<Booking> bookings = new List<Booking>();
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetAllBooks", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Booking booking = new Booking
                        {
                            BookingId = Convert.ToInt32(reader["BookingId"]),
                            CustomerId = Convert.ToInt32(reader["CustomerId"]),
                            Type = reader["Type"].ToString(),
                            BookedDateTime = Convert.ToDateTime(reader["BookedDateTime"]),
                            OriginDateTime = Convert.ToDateTime(reader["OriginDateTime"]),
                            Status = reader["Status"].ToString()
                        };

                        //Following are possible nullable columns

                        if (reader["SupervisorId"] != DBNull.Value)
                        {
                            booking.SupervisorId = Convert.ToInt32(reader["SupervisorId"]);
                        }

                        if (reader["TravelledDistance"] != DBNull.Value)
                        {
                            booking.TravelledDistance = Convert.ToInt32(reader["TravelledDistance"]);
                        }

                        if (reader["DeliveredDateTime"] != DBNull.Value)
                        {
                            booking.DeliveredDateTime = Convert.ToDateTime(reader["DeliveredDateTime"]);
                        }

                        bookings.Add(booking);
                    }
                }
            }
            return bookings;
        }

        /// <summary>
        ///     Get all bookings by booking Id
        /// </summary>
        /// <param name="bookingId">Represent's the booking Id</param>
        /// <returns>Booking</returns>
        public static Booking GetBooking(int bookingId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetBookingById", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Booking booking = new Booking
                        {
                            BookingId = bookingId,
                            CustomerId = Convert.ToInt32(reader["CustomerId"]),
                            Type = reader["Type"].ToString(),
                            BookedDateTime = Convert.ToDateTime(reader["BookedDateTime"]),
                            OriginDateTime = Convert.ToDateTime(reader["OriginDateTime"]),
                            Status = reader["Status"].ToString()
                        };

                        //Following are possible nullable columns

                        if (reader["SupervisorId"] != DBNull.Value)
                        {
                            booking.SupervisorId = Convert.ToInt32(reader["SupervisorId"]);
                        }

                        if (reader["TravelledDistance"] != DBNull.Value)
                        {
                            booking.TravelledDistance = Convert.ToInt32(reader["TravelledDistance"]);
                        }

                        if (reader["DeliveredDateTime"] != DBNull.Value)
                        {
                            booking.DeliveredDateTime = Convert.ToDateTime(reader["DeliveredDateTime"]);
                        }

                        return booking;
                    }
                }
            }
            return null;
        }

        /// <summary>
        ///     Get all booking type by booking id
        /// </summary>
        /// <param name="bookingId">Represent's the booking Id</param>
        /// <returns>Booking type</returns>
        public static string GetBookingType(int bookingId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spGetBookingType", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);

                connection.Open();
                object type = command.ExecuteScalar();

                return type == null ? null : type.ToString();
            }
        }

        /// <summary>
        ///     Change booking status by booking id
        /// </summary>
        /// <param name="bookingId">Represent's the booking Id</param>
        /// <param name="status">Represent's the booking status</param>
        public static void ChangeStatus(int bookingId, string status)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spChangeBookingStatus", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);
                command.Parameters.AddWithValue("@Status", status);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Reset booking confirmation (Roll back to pending)
        /// </summary>
        /// <param name="bookingId">Represent's the booking Id</param>
        public static void ResetConfirmation(int bookingId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spResetBookingConfirmation", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Confirm the booking
        /// </summary>
        /// <param name="booking">Represent's the booking</param>
        public static void ApproveBooking(Booking booking)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spApproveBooking", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", booking.BookingId);
                command.Parameters.AddWithValue("@SupervisorId", booking.SupervisorId);
                command.Parameters.AddWithValue("@ApproximateEndDateTime", booking.ApproximateEndDateTime);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Settle or complete booking
        /// </summary>
        /// <param name="booking">Represent's the booking</param>
        public static void SettleBooking(Booking booking)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand command = new SqlCommand("spSettleBooking", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", booking.BookingId);
                command.Parameters.AddWithValue("@TravelledDistance", booking.TravelledDistance);
                command.Parameters.AddWithValue("@DeliveredDateTime", booking.DeliveredDateTime);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public static int GetNofificationCount(int customerId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("spGetNotificationCount", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@CustomerId", customerId);

                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public static List<Booking> GetNofification(int customerId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("spGetNotification", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@CustomerId", customerId);

                SqlDataReader rdr = command.ExecuteReader();
                List<Booking> bookings = new List<Booking>();
                while (rdr.Read())
                {
                    Booking booking = new Booking();
                    CurrentLocation location = new CurrentLocation();
                    booking.BookingId = Convert.ToInt32(rdr["BookingId"]);
                    booking.Type = rdr["Type"].ToString();
                    booking.BookedDateTime = (DateTime) rdr["BookedDateTime"];
                    booking.OriginDateTime = (DateTime) rdr["OriginDateTime"];
                    location.City = rdr["City"].ToString();
                    booking.Status = rdr["Status"].ToString();
                    booking.OriginLocation = location;
                    bookings.Add(booking);
                }
                return bookings;
            }
        }

        public static void ClearNofification(int bookingId)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand("spClearNotification", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@BookingId", bookingId);
                command.ExecuteNonQuery();
            }
        }
    }
}